#!/usr/bin/env python3
"""
本地环境设置脚本
用于在没有Docker的情况下设置SQLite数据库
"""

import os
import sqlite3
from pathlib import Path


def create_local_database():
    """创建本地SQLite数据库"""
    db_path = Path("data/smarttrader.db")
    db_path.parent.mkdir(exist_ok=True)
    
    # 创建数据库连接
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 创建用户表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            subscription_type VARCHAR(20) DEFAULT 'free',
            is_active BOOLEAN DEFAULT 1,
            is_superuser BOOLEAN DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建股票表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS stocks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code VARCHAR(20) UNIQUE NOT NULL,
            name VARCHAR(100) NOT NULL,
            market VARCHAR(10) NOT NULL,
            industry VARCHAR(50),
            sector VARCHAR(50),
            listing_date DATE,
            total_shares DECIMAL(20, 2),
            float_shares DECIMAL(20, 2),
            market_cap DECIMAL(20, 2),
            pe_ratio DECIMAL(10, 2),
            pb_ratio DECIMAL(10, 2),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建用户自选股表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS user_watchlist (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            stock_id INTEGER NOT NULL,
            added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id),
            FOREIGN KEY (stock_id) REFERENCES stocks (id),
            UNIQUE(user_id, stock_id)
        )
    ''')
    
    # 创建股票日K数据表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS stock_daily (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            stock_id INTEGER NOT NULL,
            trade_date DATE NOT NULL,
            open_price DECIMAL(10, 3),
            high_price DECIMAL(10, 3),
            low_price DECIMAL(10, 3),
            close_price DECIMAL(10, 3),
            volume BIGINT,
            amount DECIMAL(20, 2),
            turnover_rate DECIMAL(8, 4),
            pe_ratio DECIMAL(10, 2),
            pb_ratio DECIMAL(10, 2),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (stock_id) REFERENCES stocks (id),
            UNIQUE(stock_id, trade_date)
        )
    ''')
    
    # 插入一些测试数据
    test_stocks = [
        ('000001.SZ', '平安银行', 'SZ', '银行', '金融'),
        ('000002.SZ', '万科A', 'SZ', '房地产', '房地产'),
        ('600000.SH', '浦发银行', 'SH', '银行', '金融'),
        ('600036.SH', '招商银行', 'SH', '银行', '金融'),
        ('000858.SZ', '五粮液', 'SZ', '食品饮料', '白酒'),
    ]
    
    cursor.executemany('''
        INSERT OR IGNORE INTO stocks (code, name, market, industry, sector)
        VALUES (?, ?, ?, ?, ?)
    ''', test_stocks)
    
    conn.commit()
    conn.close()
    
    print(f"✅ 本地数据库创建成功: {db_path.absolute()}")
    print("✅ 测试数据插入完成")


def update_env_for_local():
    """更新环境变量文件为本地配置"""
    env_content = """# 应用配置
DEBUG=true
SECRET_KEY=dev-secret-key-change-this-in-production-environment
ENVIRONMENT=development

# 本地SQLite数据库配置
DATABASE_URL=sqlite:///./data/smarttrader.db

# Redis配置（可选，本地开发可以不用）
REDIS_URL=redis://localhost:6379

# 数据源API配置
TUSHARE_TOKEN=
ALPHA_VANTAGE_KEY=

# 日志配置
LOG_LEVEL=INFO
"""
    
    with open('.env', 'w', encoding='utf-8') as f:
        f.write(env_content)
    
    print("✅ 环境配置文件已更新为本地模式")


if __name__ == "__main__":
    print("🚀 正在设置本地开发环境...")
    create_local_database()
    update_env_for_local()
    print("🎉 本地环境设置完成！")
    print("\n📝 下一步:")
    print("1. 运行 start.bat 启动后端服务")
    print("2. 访问 http://localhost:8000/docs 查看API文档")